package com.xl.competition.old.task2

import org.apache.spark.sql.SparkSession

/**
 * @author: xl
 * @createTime: 2023/11/12 18:54:39
 * @program: com.xl.competition
 * @description: ${description}
 */
object LoadCustomerToDim {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")
    //初始化sparkSession对象
    val sc: SparkSession = SparkSession
      .builder() //利用builder方法构建
      .master("local[*]") //本地模式运行下的线程资源  提交集群时不写
      .appName(this.getClass.getName) //应用名字  方便日志跟踪
      .enableHiveSupport() //支持hive   因为要写到hive表中
      .config("hive.exec.dynamic.partition.mode", "nonstrict")
      .config("hive.metastore.uris", "thrift://master:9083") //hive的元数据地址 提交集群可不写但要有hive的环境变量
      .getOrCreate()

    sc.sql(
      """
        |create table if not exists dim.dim_customer
        |(
        |    `custkey`       int,
        |    `NAME`          string,
        |    `gender`        string,
        |    `address`       string,
        |    `nationkey`     int,
        |    `phone`         string,
        |    `mktsegment`    string,
        |    `acctbal`       decimal(12, 2),
        |    `times`         bigint,
        |    `datime`        string,
        |    dwd_insert_user string,
        |    dwd_insert_time string,
        |    dwd_modify_user string,
        |    dwd_modify_time string
        |) PARTITIONED BY (`etldate` STRING)
        |    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        |        NULL DEFINED AS ''
        |    LOCATION '/warehouse/competition/dim/dim_customer/'
        |    TBLPROPERTIES ('orc.compress' = 'snappy')
        |""".stripMargin)

    sc.sql(
      """
        |insert overwrite table dim.dim_customer partition(etldate)
        |select custkey,
        |       name,
        |       gender,
        |       address,
        |       nationkey,
        |       phone,
        |       mktsegment,
        |       acctbal,
        |       times,
        |       `if`(length(datime)==10,concat(datime," 00:00:00"),datime),
        |       'user1'                                     dwd_insert_user,
        |       'user1'                                     dwd_modify_user,
        |       from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |       from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
        |       substring(datime, 0, 10)                    etldate
        |from ods.customer
        |""".stripMargin)

    sc.stop()
  }
}
